Imports System.Data.OleDb
Imports DTO

Public Class NhapDiemDAO
    Inherits DataProviderDAO
    Private Shared heso As Integer = 0
    Private Shared dtb As Double
    Private Shared d15phut As Double
    Private Shared d1tiet As Double
    Private Shared dcuoiki As Double

    Public Function LayBang() As DataTable
        Dim dt As DataTable
        Dim sql As String
        sql = "select * from Diem"
        dt = Execute(sql)
        Return dt
    End Function
    Public Function HocKi() As DataTable
        Dim dt As DataTable
        Dim sql As String
        sql = "select distinct HocKy from Diem"
        dt = Execute(sql)
        Return dt
    End Function
    Public Function LoaiKiemTra() As DataTable
        Dim dt As DataTable
        Dim sql As String
        sql = "select MaLKT,TenLKT from LoaiKiemTra"
        dt = Execute(sql)
        Return dt
    End Function
    Public Function Lop(ByVal user As String) As DataTable
        Dim dt As DataTable
        Dim sql As String
        sql = "select lh.MaLH,lh.TenLH from LopHoc lh,GiaoVien gv, PhanCong pc, NguoiDung nd where lh.MaLH=pc.MaLH and pc.MaGV=gv.MaGV and gv.MaU=nd.MaU and nd.TenUser like '" & user & "' "
        dt = Execute(sql)
        Return dt
    End Function
    Public Function girdNhapDiem(ByVal MaLH As String) As DataTable
        Dim dt As DataTable
        Dim sql As String
        sql = " select hs.HoTen, hs.NgaySinh from HocSinh hs where hs.MaLH=" + MaLH
        dt = Execute(sql)
        Return dt
    End Function
    Public Function NamHienTai() As String
        Dim dt As DataTable
        Dim sql As String
        sql = "select top 1 MaNH from NamHoc group by MaNH order by MaNH desc"
        dt = Execute(sql)
        Return dt.Rows(0)(0)
    End Function
    Public Function MaMonHoc(ByVal user As String) As String
        Dim dt As DataTable
        Dim sql As String
        sql = "select mh.MaMH from MonHoc mh,GiaoVien gv,NguoiDung nd where nd.MaU=gv.MaU and gv.MaMH=mh.MaMH and nd.TenUser ='" + user + "'"
        dt = Execute(sql)
        Return dt.Rows(0)(0)
    End Function
    Public Function MaDiem() As String
        Dim dt As DataTable
        Dim sql As String
        sql = "select top 1 MaD from Diem group by MaD order by MaD desc"
        dt = Execute(sql)
        If (dt.Rows.Count > 0) Then
            Return (dt.Rows(0)(0) + 1).ToString
        End If
        Return 0
    End Function
    Public Function ChkCapNhat(ByVal MaLop As String, ByVal MaLKT As String, ByVal HocKy As String, ByVal MaMH As String) As DataTable
        Dim dt As DataTable
        Dim sql As String
        sql = "select hs.MaHS,hs.HoTen,dm.Diem from HocSinh hs,LopHoc lh,Diem dm where lh.MaLH=hs.MaLH and hs.MaHS=dm.MaHS and dm.MaMH=" + MaMH + " and lh.MaLH=" + MaLop + " and lh.MaNam=" + NamHienTai() + " and dm.MaLKT=" + MaLKT + " and dm.HocKy=" + HocKy
        dt = Execute(sql)
        Return dt
    End Function
    Public Sub NhapDiem(ByVal d As DiemDTO)
        Dim sql As String
        sql = "insert into Diem values('" + d.MaDiem.ToString + "','" + d.Diem.ToString + "','" + d.HocKy.ToString + "','" + d.MaLoaiKiemTra.ToString + "','" + d.MaMH.ToString + "','" + d.MaHS.ToString + "')"
        ExecuteNonQuery(sql)
    End Sub
    Public Sub CapNhat(ByVal d As DiemDTO)
        Dim sql As String
        sql = "update Diem set Diem=" + d.Diem.ToString + " where MaHS=" + d.MaHS.ToString + " and MaLKT=" + d.MaLoaiKiemTra.ToString + " and HocKy=" + d.HocKy.ToString + " and MaMH=" + d.MaMH.ToString
        ExecuteNonQuery(sql)
    End Sub
    Public Function TrungBinhMon(ByVal MaHocSinh As String, ByVal HocKi As String, ByVal MaMonHoc As String) As Double
        Dim dt As DataTable
        Dim sql As String
        d15phut = 0
        d1tiet = 0
        dcuoiki = 0
        heso = 0
        dtb = 0
        sql = "select dm.Diem from HocSinh hs,Diem dm where hs.MaHS=dm.MaHS and dm.MaLKT=1 and dm.MaMH=" + MaMonHoc + " and hs.MaHS=" + MaHocSinh + " and dm.HocKy=" + HocKi
        dt = Execute(sql)
        If (dt.Rows.Count > 0) Then
            For i As Integer = 0 To dt.Rows.Count - 1
                d15phut = d15phut + dt.Rows(i)(0)
                heso = heso + 1
            Next
        End If
        sql = "select dm.Diem from HocSinh hs,Diem dm where hs.MaHS=dm.MaHS and dm.MaLKT=2 and dm.MaMH=" + MaMonHoc + " and hs.MaHS=" + MaHocSinh + " and dm.HocKy=" + HocKi
        dt = Execute(sql)
        If (dt.Rows.Count > 0) Then
            For i As Integer = 0 To dt.Rows.Count - 1
                d1tiet = d1tiet + 2 * dt.Rows(i)(0)
                heso = heso + 2
            Next
        End If
        sql = "select dm.Diem from HocSinh hs,Diem dm where hs.MaHS=dm.MaHS and dm.MaLKT=3 and dm.MaMH=" + MaMonHoc + " and hs.MaHS=" + MaHocSinh + " and dm.HocKy=" + HocKi
        dt = Execute(sql)
        If (dt.Rows.Count > 0) Then
            For i As Integer = 0 To dt.Rows.Count - 1
                dcuoiki = dcuoiki + 2 * dt.Rows(i)(0)
                heso = heso + 2
            Next
        End If
        Return Math.Round((d15phut + d1tiet + dcuoiki) / heso, 2)
    End Function
    Public Sub CapNhatDiemTBMon(ByVal MaHocSinh As String, ByVal HocKi As String, ByVal MaMonHoc As String)
        Dim sql As String
        sql = "Update DiemTrungBinh dtb set dtb.DiemTB=" + TrungBinhMon(MaHocSinh, HocKi, MaMonHoc).ToString + " where dtb.MaHS=" + MaHocSinh + " and dtb.HocKy=" + HocKi + _
        " and dtb.MaMH=" + MaMonHoc
        ExecuteNonQuery(sql)
    End Sub
End Class



